//
//  DataBaseHandler.m
//  UI21_SQLite
//
//  Created by dllo on 16/1/11.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandler.h"
#import <sqlite3.h>
#import "ModelForVC2.h"
@implementation DataBaseHandler

#pragma mark - 知识点1 单例

+ (instancetype)shareDataBaseHandler {
    
    // 在静态区, 只初始化一次.
    static DataBaseHandler *dataBase = nil;
    
    if (dataBase == nil) {
        
        dataBase = [[DataBaseHandler alloc] init];
    
    }
    
    return dataBase;
    
}
// 定义一个sqlite结构体(对象)
sqlite3 *db;

#pragma mark - 知识点2: 打开数据库.
- (void)open {
    
    if (db != nil) {
        
        NSLog(@"数据库已经开启!!!");
        
        return;
    }
    // API: sqlite3_open
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"JunPing.sqlite"];
    
    /** 
     * @param filename 数据库文件的路径(注意,类型为C字符串)
     * @param ppDb 数据库对象
     */
    
   int result = sqlite3_open(file.UTF8String,&db);
    
    if (result == SQLITE_OK) {
       
        NSLog(@"数据库打开成功");
        
    } else {
        
        NSLog(@"数据库打开失败, code = %d", result);
     
    }
    
}

#pragma mark - 知识点3: 创建表格.
- (void)createTable {
    // API:sqlite3_exec
    
    // 创建SQL语句
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS NewsLike(number INTEGER PRIMARY KEY AUTOINCREMENT, Title text, NewsSummary text, NewsUrl text, NewsCommentNum text)";
    
    int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"创建表格成功!");
        
    } else {
        
        NSLog(@"创建表格失败. code:%d", result);
   
    }
 
}

#pragma mark - 知识点4: 插入数据.
- (void)insertModelYaoWen:(ModelForVC2 *)modelvc2 {
    
    // API: sqlite3_exec
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into NewsLike(Title, NewsSummary, NewsUrl, NewsCommentNum) VALUES('%@', '%@', '%@', '%@')", modelvc2.Title, modelvc2.NewsSummary, modelvc2.NewsUrl, modelvc2.NewsCommentNum];
    
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"添加数据完成");
        
    } else {
        
        NSLog(@"添加数据失败. code:%d", result);
        
    }
    
}

#pragma mark - 知识点6: 删除数据
- (void)deleteModelWithTitle:(NSString *)title {
    
    // API: sqlite3_exec
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM NewsLike WHERE Title = '%@'", title];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除成功");
        
    } else {
        
        NSLog(@"删除失败, code:%d", result);
        
    }

}
#pragma mark - 知识点7 查询数据
- (NSArray *)selectAll {
    
    // 存放符合条件的数据(元素是model对象)
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql语句
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT *FROM NewsLike"];
    
    // API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column,
    
    // 声明一个stmt对象(结构体)
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"准备就绪, 查询中......");
        
        // 每行都执行查询语句
        while(sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 如果查询条件匹配, 通过sqlite3_column_函数簇 取出值.
            
            // 取出那一列的数据值
            const unsigned char *Title = sqlite3_column_text(stmt, 1);
            
            const unsigned char *NewsSummary = sqlite3_column_text(stmt, 2);
            
            const unsigned char *NewsUrl = sqlite3_column_text(stmt, 3);
            
            const unsigned char *NewsCommentNum = sqlite3_column_text(stmt, 4);
            
            // 创建model对象, 赋值之后放入数组中.
            ModelForVC2 *modelVc2 = [[ModelForVC2 alloc] init];
            
            modelVc2.Title = [NSString stringWithUTF8String:(const char *)Title] ;
            
            modelVc2.NewsSummary = [NSString stringWithUTF8String:(const char *)NewsSummary];
            
            modelVc2.NewsUrl = [NSString stringWithUTF8String:(const char *) NewsUrl];
            
            modelVc2.NewsCommentNum = [NSString stringWithUTF8String:(const char *)NewsCommentNum];
            
            [arr addObject:modelVc2];
            
            [modelVc2 release];
            
        }
        
        // 销毁对象
        sqlite3_finalize(stmt);
        
    } else {
        
        NSLog(@"不能正常查询. code:%d", result);
        
        // 销毁对象
        sqlite3_finalize(stmt);
        
    }
    return arr;
    
}
#pragma mark - 知识点8: 删除表单
- (void)dropTable {
    
    NSString *dropSQL = @"DROP TABLE lanou1130";
    
    int result = sqlite3_exec(db, dropSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除表格成功");
        
    } else {
        
        NSLog(@"删除表格失败");
        
    }
    
}

#pragma mark - 知识点9: 关掉数据库
- (void)closeDB {
    
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        
        NSLog(@" 数据库关闭");
        
        db = nil;
        
    } else {
        
        NSLog(@"数据库关闭失败. code:%d", result);
        
    }
    
}









@end
